<?php
define('ORA_CHARSET_DEFAULT', 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK');
define('ORA_CONNECTION_TYPE_DEFAULT', 1);
define('ORA_CONNECTION_TYPE_PERSISTENT', 2);
define('ORA_CONNECTION_TYPE_NEW', 3);
define('ORA_MESSAGES_NOT_CONNECTED', 'Not connected to Oracle instance');

class OracleOCI {
    private static $_instance;
    private $conn_handle;
    private $conn_data;
    private $errors_pool;
    private $statements = array();
    private $autocommit = false;
    private $fetch_mode = OCI_BOTH;
    private $last_query;
    private $var_max_size = 1000;
    private $execute_status = false;
    private $charset;
    private $session_mode = OCI_DEFAULT;
    private $db_host;
    private $db_user;
    private $db_pass;

    /**
    * Set array fetching mode for Fetch methods
    *
    * @param mixed $mode
    */
    public function SetFetchMode($mode = OCI_BOTH){
        $this->fetch_mode = $mode;
    }

    /**
    * Set on|off auto commit option
    *
    * @param bool $mode
    */
    public function SetAutoCommit($mode = true){
        $this->autocommit = $mode;
    }

    /**
    * Set variable max size for binding
    *
    * @param int $size
    */
    public function SetVarMaxSize($size){
        $this->var_max_size = $size;
    }

    /**
    * Returns the last error found.
    *
    */
    public function GetError(){
        return @oci_error($this->conn_handle);
    }

    /**
    * Set nls_lang
    *
    * @param string $charset
    */
    public function SetNlsLang($charset = ORA_CHARSET_DEFAULT){
        $this->charset = $charset;
    }

    /**
    * Constructor
    *
    */
    public function __construct(){
        $this->SetNlsLang();
        $this->SetAutoCommit();
        $this->SetFetchMode(OCI_ASSOC+OCI_RETURN_NULLS);
    }

    /**
    * Connect to Oracle DB
    *
    * @param string $host
    * @param string $user
    * @param string $pass
    * @param int $mode (OCI_DEFAULT, OCI_SYSDBA, OCI_SYSOPER)
    * @param int $type (ORA_CONNECTION_TYPE_DEFAULT, ORA_CONNECTION_TYPE_NEW, ORA_CONNECTION_TYPE_PERSISTENT)
    * @return bool
    */
    public function Connect($host = '', $user='', $pass='', $mode = OCI_DEFAULT, $type = ORA_CONNECTION_TYPE_DEFAULT){
        if (!empty($host)) $this->db_host = $host;
        if (!empty($user)) $this->db_user = $user;
        if (!empty($pass)) $this->db_pass = $pass;
        switch ($type) {
            case ORA_CONNECTION_TYPE_PERSISTENT:
                $this->conn_handle = @oci_pconnect($this->db_user, $this->db_pass, $this->db_host, $this->charset, $mode);
                break;
            case ORA_CONNECTION_TYPE_NEW:
                $this->conn_handle = @oci_new_connect($this->db_user, $this->db_pass, $this->db_host, $this->charset, $mode);
                break;
            default:
                $this->conn_handle = @oci_connect($this->db_user, $this->db_pass, $this->db_host, $this->charset, $mode);
        }
        if (!is_resource($this->conn_handle)) {
            $this->ShowError('Oracle Connect Error');
        }
        return is_resource($this->conn_handle) ? true : false;
    }

    /**
    * Destructor
    *
    */
    public function __destruct(){
        if (is_resource($this->conn_handle)) {
            @oci_close($this->conn_handle);
        }
    }

    /**
    * This function return last command exec status
    *
    * @return bool
    */
    public function GetExecuteStatus(){
        return $this->execute_status;
    }

    private function GetBindingType($var){
        if (is_a($var, "OCI-Collection")) {
            $bind_type = SQLT_NTY;
            $this->SetVarMaxSize(-1);
        } elseif (is_a($var, "OCI-Lob")) {
            $bind_type = SQLT_CLOB;
            $this->SetVarMaxSize(-1);
        } else {
            $bind_type = SQLT_CHR;
        }
        return $bind_type;
    }

    public function execute($sql_text, $bind = array(), $object = true){
        if (empty($sql_text)){
            return false;
        }
        $clob = false;
        foreach ($bind as $value) {
            if (isset($value[2]) && $value[2] == 'clob') {
                $clob = true;
                break;
            }
        }
        if ($clob) {
            $rs = $this->CursorClob($sql_text, $bind);
        } else {
            $rs = $this->Cursor($sql_text, $bind);
        }
        if (is_resource($rs)) {
            $rs = $this->GetArray($rs, $object);
        }
        return $rs;
    }

    public function executeFirst($sql_text, $bind = array(), $object = true) {
        $rs = $this->Execute($sql_text, $bind, $object);
        if (isset($rs[0])) {
            $rs = $rs[0];
        }
        return $rs;
    }

    public function GetArray($rs, $object = true){
        $array = array();
        while ($row = $this->FetchArray($rs)){
            $tmpRow = array();
            if(!empty($row)){
                foreach ($row as $col => $value) {
                    $tmpRow[strtolower($col)] = is_object($value) ? $value->load() : (!isset($value) ? "" : $value);
                }
                $array[] = $object ? (object) $tmpRow : $tmpRow;
            }
        }
        return $array;
    }

    public function GetRow($rs){
        $data = $this->FetchArray($rs);
        $tmpRow = array();
        if(!empty($data)){
            foreach ($data as $key => $value) {
                $tmpRow[strtolower($key)] = is_object($value) ? $value->load() : (!isset($value) ? "" : $value);
            }
        }
        return $tmpRow;
    }

    /**
     * ShowError
     */
    private function ShowError($message = '', $sql = '', $params = array(), $stmt = null){
        if (empty($message)) {
            $message = 'DB Error';
        }
        $error = '';
        if ($stmt) {
            $ociErr = oci_error($stmt);
            $error = isset($ociErr["message"]) ? $ociErr["message"] : "";
        }
        // $content = "[MSG] $message\n[ERROR] $error\n[SQL] $sql\n[PARAMS]".var_export($params, true);
        // echo "Error: $content";
        // writeLog($content, 'db');
        return array(
            "success" => 0,
            "msg" => "Oracle Error",
            "data" => array(
                "MSG" => $message,
                "ERROR" => $error,
                "SQL" => $sql,
                "PARAMS" => $params
            )
        );
    }

    /**
    * Fetch array of select statement
    *
    * @param resource $statement valid OCI statement id
    * @return array
    */
    public function FetchArray($statement){
        return oci_fetch_array($statement, $this->fetch_mode);
    }

    /**
    * Function Returns a numerically indexed array containing the next result-set row of a query.
    * Each array entry corresponds to a column of the row. This function is typically called in a loop
    * until it returns FALSE, indicating no more rows exist.
    *
    * @param resource $statement valid OCI statement id
    * @return array Returns a numerically indexed array. If there are no more rows in the statement then FALSE is returned.
    */
    public function FetchRow($statement){
        return oci_fetch_row($statement);
    }

    /**
    * Fetch rows from select operation
    *
    * @param resourse $statement valid OCI statement identifier
    * @param int $skip number of initial rows to ignore when fetching the result (default value of 0, to start at the first line).
    * @param int $maxrows number of rows to read, starting at the skip th row (default to -1, meaning all the rows).
    * $return array
    */
    public function FetchAll($statement, $skip = 0, $maxrows = -1){
        $rows = array();
        oci_fetch_all($statement, $rows, $skip, $maxrows, OCI_FETCHSTATEMENT_BY_ROW);
        return $rows;
    }

    /**
    * Fetch row as object
    *
    * @param resource $statement valid OCI statement identifier
    * @return object
    * @author Sergey Pimenov
    */
    public function FetchObject($statement){
        return oci_fetch_object($statement);
    }

    /**
    * Fetches the next row (for SELECT statements) into the internal result-buffer.
    *
    * @param resource $statement valid OCI statement id
    * @return bool
    */
    public function Fetch($statement){
        return oci_fetch($statement);
    }

    /**
    * Returns the data from field in the current row, fetched by Fetch()
    *
    * @param resource $statement valid OCI statement id
    * @param mixed $field Can be either use the column number (1-based) or the column name (in uppercase).
    * @return mixed
    */
    public function Result($statement, $field){
        return oci_result($statement, $field);
    }

    /**
    * Associates a PHP variable with a column for query fetches using Fetch().
    *
    * @param resource $statement A valid OCI statement identifier
    * @param string $column_name The column name used in the query.
    * @param mixed $variable The PHP variable that will contain the returned column value.
    * @param int $type The data type to be returned.
    * @return bool
    */
    public function DefineByName($statement , $column_name , &$variable, $type = SQLT_CHR){
        return oci_define_by_name($statement, $column_name, $variable, $type);
    }

    public function FieldIsNull($statement, $field){
        return oci_field_is_null($statement, $field);
    }

    public function FieldName($statement, int $field){
        return oci_field_name($statement, $field);
    }

    public function FieldPrecition($statement, int $field){
        return oci_field_precision($statement, $field);
    }

    public function FieldScale($statement, int $field){
        return oci_field_scale($statement, $field);
    }

    public function FieldSize($statement, $field){
        return oci_field_size($statement, $field);
    }

    public function FieldTypeRaw($statement, int $field){
        return oci_field_type_raw($statement, $field);
    }

    public function FieldType($statement, int $field){
        return oci_field_type($statement, $field);
    }

    /**
    * Gets the number of rows affected during statement execution.
    *
    * @param resource $statement
    * @return int
    */
    public function NumRows($statement){
        return oci_num_rows($statement);
    }

    /**
    * Synonym for NumRows()
    *
    * @param resource $statement
    * @return int
    */
    public function RowsAffected($statement){
        return $this->NumRows($statement);
    }

    /**
    * Gets the number of columns in the given statement.
    *
    * @param resource $statement
    * @return int
    */
    public function NumFields($statement){
        return oci_num_fields($statement);
    }

    /**
    * Synonym for NumFields()
    *
    * @param resource $statement
    * @return int
    */
    public function FieldsCount($statement){
        return $this->NumFields($statement);
    }

    // Support Lob

    /**
    * Allocates resources to hold descriptor or LOB locator.
    *
    * @param resource $connection
    * @param int $type Valid values for type are: OCI_DTYPE_FILE, OCI_DTYPE_LOB and OCI_DTYPE_ROWID.
    * @return OCI-Lob
    */
    public function NewDescriptor($type = OCI_DTYPE_LOB){
        return oci_new_descriptor($this->conn_handle, $type);
    }

    /**
    * Allocates a new collection object
    *
    * @param string $typename Should be a valid named type (uppercase).
    * @param string $schema Should point to the scheme, where the named type was created. The name of the current user is the default value.
    * @return OCI-Collection
    */
    public function NewCollection($typename, $schema = null){
        return oci_new_collection($this->conn_handle, $typename, $schema);
    }

    // Support stored procedures and functions

    /**
    * Method for execute stored procedure
    *
    * @param mixed $name
    * @param string $params
    * @param mixed $bind
    * @return resource
    */
    public function StoredProc($name, $params = false, &$bind = false){
        if ($params) {
          if (is_array($params)) $params = implode(",", $params);
          $sql = "begin $name($params); end;";
        } else {
          $sql = "begin $name; end;";
        }
        return $this->Execute($sql, $bind);
    }

    /**
    * Methos for execute stored function
    *
    * @param mixed $name
    * @param string $params
    * @param mixed $bind
    * @return mixed
    */
    public function Func($name, $params = false, $bind = false){
        if ($params) {
          if (is_array($params)) $params = implode(",", $params);
          $sql = "select $name($params) as RESULT from dual";
        } else {
          $sql = "select $name from dual";
        }
        $h = $this->Execute($sql, $bind);
        $r = $this->FetchArray($h);
        return $r['RESULT'];
    }

    /**
    * Method execute cursor defined in stored proc
    *
    * @param string $stored_proc stored proc where cursor is defined
    * @param string $bind binding for out parameter in stored proc
    * @return resource
    * @example Cursor("utils.get_cursor", "dataset"); //begin utils.get_cursor(:dataset); end;
    * @param array $bind array(array(name,value,length));
    */
    public function Cursor($stored_proc, $bind){
        if (!is_resource($this->conn_handle))
            $this->Connect();
        if (!is_resource($this->conn_handle)) {
            $curs = $this->ShowError('Oracle Connect Error');
            return $curs;
        }
        $param = "";
        foreach($bind as $v){
            $param .= ":$v[0], ";
        }
        $sql = "begin $stored_proc($param :curs); end;";
        $this->last_query = $sql;
        $outParam = false;
        $curs = oci_new_cursor($this->conn_handle);
        $stmt = oci_parse($this->conn_handle, $sql);
        if (!$stmt) {
            $this->__destruct();
            $this->Connect();
            $stmt = oci_parse($this->conn_handle, $sql);
        }
        if (!$stmt) {
            $curs = $this->ShowError('Oracle Parse Error', $sql, json_encode($bind));
            return $curs;
        }
        $com_mode = $this->autocommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT;

        foreach($bind as $v){
            if($v[0] != 'out'){
                oci_bind_by_name($stmt, $v[0], $v[1], -1);
            }else{
                $outParam = true;
                $curOut = oci_new_cursor($this->conn_handle);
                oci_bind_by_name($stmt, "out", $curOut, -1, OCI_B_CURSOR);
            }
        }
        oci_bind_by_name($stmt, "curs", $curs, -1, OCI_B_CURSOR);

        $this->execute_status = oci_execute($stmt, $com_mode);
        if($outParam){
             $this->execute_status_curs = oci_execute($curOut, $com_mode);
        }
        $this->execute_status_curs = oci_execute($curs, $com_mode);
        if(!$this->execute_status || !$this->execute_status_curs){
            $curs = $this->ShowError('Oracle Execute Error', $sql, $bind, $stmt);
        }
        $this->FreeStatement($stmt);
        if (empty($curs)) {
            $curs = array("success" => 0, 'msg' => 'DB Error');
        }
        return $outParam ? array('data'=>$curs, 'out'=>$curOut) : $curs;
    }

    /**
    * Method execute cursor defined in stored proc with clob parameter
    *
    * @param string $stored_proc stored proc where cursor is defined
    * @param string $bind binding for out parameter in stored proc
    * @return resource
    * @example CursorClob("utils.get_cursor", "dataset"); //begin utils.get_cursor(:dataset); end;
    * @param array $bind array(array(name,value,length));
    */
    public function CursorClob($stored_proc, $bind){
        if (!is_resource($this->conn_handle))
            $this->Connect();
        if (!is_resource($this->conn_handle)) {
            $curs = $this->ShowError('Oracle Connect Error');
            return $curs;
        }
        $pars = "";
        foreach($bind as $p){
            $pars .= ":".$p[0].",";
        }
        $sql = "begin $stored_proc($pars :curs); end;";
        $this->last_query = $sql;
        $curs = oci_new_cursor($this->conn_handle);
        $stmt = oci_parse($this->conn_handle, $sql);
        if (!$stmt) {
            $this->__destruct();
            $this->Connect();
            $stmt = oci_parse($this->conn_handle, $sql);
        }
        if (!$stmt) {
            $curs = $this->ShowError('Oracle Parse Error', $sql, json_encode($bind));
            return $curs;
        }
        $com_mode = OCI_DEFAULT;
        try {
            $clobs = array();
            foreach($bind as $p){
                $pName = $p[0];
                $pValue = $p[1];
                $pType = isset($p[2]) ? strtolower($p[2]) : '';
                if($pType != 'clob'){
                    oci_bind_by_name($stmt, $pName, $p[1], -1);
                }else{
                    $clobs[$pName] = oci_new_descriptor($this->conn_handle, OCI_D_LOB);
                    oci_bind_by_name($stmt, $pName, $clobs[$pName], -1, OCI_B_CLOB);
                    $clobs[$pName]->writeTemporary($pValue, OCI_TEMP_CLOB);
                }
            }
            oci_bind_by_name($stmt, "curs", $curs, -1, OCI_B_CURSOR);

            $this->execute_status = oci_execute($stmt, $com_mode);
            $this->execute_status_curs = oci_execute($curs, $com_mode);
            if($this->execute_status && $this->execute_status_curs){
                $rsp = $curs;
            }else{
                $rsp = $this->ShowError('Oracle Execute Error', $sql, $bind, $stmt);
            }
            foreach($clobs as $clob){
                $clob->close();
            }
        } catch (Exception $e) {
            $rsp = $this->ShowError($e->getMessage(), $sql, $bind, $stmt);
        }
        $this->FreeStatement($stmt);
        if (empty($rsp)) {
            $rsp = array("success" => 0, 'msg' => 'DB Error');
        }
        return $rsp;
    }

    /**
    * Invalidates a cursor, freeing all associated resources and cancels the ability to read from it.
    *
    * @param resource $statement valid OCI statement id
    * @return bool
    */
    public function Cancel($statement){
        return oci_cancel($statement);
    }

    /**
    * Free resource of OCI statement identifier
    *
    * @param resource $stid
    * @return bool
    * @author Sergey Pimenov
    */
    public function FreeStatement($stid){
        unset($this->statements[$stid]);
        return oci_free_statement($stid);
    }

    /**
    * Free array of resources of OCI statement identifier
    *
    * @param array $array_stid
    * @return bool
    * @author Sergey Pimenov
    */
    public function FreeStatements($array_stid){
        if (is_array($array_stid)) foreach($array_stid as $stid) {
          unset($this->statements[$stid]);
          oci_free_statement($stid);
        }
        return true;
    }

    /**
    * Commit transaction
    *
    * @return bool
    * @author Sergey Pimenov
    */
    public function Commit(){
        if (is_resource($this->conn_handle))
          return @oci_commit($this->conn_handle);
        else
          return false;
    }

    /**
    * Rollback transaction
    *
    * @return bool
    * @author Sergey Pimenov
    */
    public function Rollback(){
        if (is_resource($this->conn_handle))
          return @oci_rollback($this->conn_handle);
        else
          return false;
    }

    /**
    * Enables or disables internal debug output.
    *
    * @param bool $mode
    */
    public function InternalDebug($mode){
        oci_internal_debug($mode);
    }


    public function GetStatement($stid){
        return $this->statements[$stid] ? $this->statements[$stid] : false;
    }

    /**
    * Get sql text operation
    *
    * @param resource $stid valid OCI statement id
    * @return string
    */
    public function QuerySnapshot($stid = false){
        if ($stid) return $this->statements[$stid]['text']; else return $this->last_query;
    }

    /**
    * Get Oracle Server version
    *
    * @return string | false
    */
    public function ServerVer(){
        if (is_resource($this->conn_handle))
          return @oci_server_version($this->conn_handle);
        else
          return false;
    }

    public function SetAction(string $action_name){
        return @oci_set_action($this->conn_handle, $action_name);
    }

    public function SetClientID(string $client_id){
        return @oci_set_client_identifier($this->conn_handle, $client_id);
    }

    public function SetClientInfo(string $client_info){
        return @oci_set_client_info($this->conn_handle, $client_info);
    }

    public function SepPrefetch(int $rows){
        return oci_set_prefetch($this->conn_handle, $rows);
    }

    /**
    * Returns a keyword identifying the type of the OCI statement.
    *
    * @param resource $statement
    * @return string (ALTER, BEGIN, CALL, CREATE, DECLARE, DELETE, DROP, INSERT, SELECT, UPDATE, UNKNOWN) return false on error
    */
    public function StatementType($statement){
        return oci_statement_type($statement);
    }

    public function DumpQueriesStack(){
        var_dump($this->statements);
    }

    public function Bye(){
        $this->__destruct();
    }

    public function get_handle(){
        return $this->conn_handle;
    }
}
?>